﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Cocbook_DTO;

namespace Cocbook_DAL
{
    public class StaffDAL
    {
        public bool CreateStaff(Staff s)
        {
            DB.SetQuery("INSERT INTO Staff VALUES(@name, @user, @pass, @position)");
            DB.AddParameters("name", s.StaffName);
            DB.AddParameters("user", s.StaffUserName);
            DB.AddParameters("pass", s.Password);
            DB.AddParameters("position", s.Position);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.CreateStaff", e.ToString());
                return false;
            }
        }

        public bool DeleteStaff(int id)
        {
            DB.SetQuery("DELETE Staff WHERE StaffID = @id");
            DB.AddParameters("id", id);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.DeleteStaff", e.ToString());
                return false;
            }
        }


        public bool UpdateStaff(Staff s)
        {
            DB.SetQuery("UPDATE Staff SET StaffName = @name, StaffUserName = @user, Password = @password, Position = @position WHERE StaffID = @id");
            DB.AddParameters("id", s.StaffID);
            DB.AddParameters("name", s.StaffName);
            DB.AddParameters("user", s.StaffUserName);
            DB.AddParameters("password", s.Password);
            DB.AddParameters("position", s.Position);
            try
            {
                DB.ExeNoneQuery();
                return true;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.UpdateStaff", e.ToString());
                return false;
            }
        }

        public int GetStaffNumber()
        {
            DB.SetQuery("SELECT COUNT(*) FROM Staff");
            try
            {
                List<List<object>> llo = DB.ExeQuery();
                int result = 0;
                if (llo.Count > 0)
                {
                    result = Convert.ToInt16(llo[0][0]);
                }
                return result;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.GetStaffNumber", e.ToString());
                return 0;
            }
        }

        public Staff GetStaffByUserName(string user)
        {
            try
            {
                DB.SetQuery("SELECT StaffID, StaffName, StaffUserName, Password, Position FROM Staff WHERE StaffUserName = @user");
                DB.AddParameters("user", user);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Staff s = new Staff();
                if (llo.Count > 0)
                {
                    s.StaffID = Convert.ToInt16(llo[0][0]);
                    s.StaffName = Convert.ToString(llo[0][1]);
                    s.StaffUserName = Convert.ToString(llo[0][2]);
                    s.Password = Convert.ToString(llo[0][3]);
                    s.Position = Convert.ToString(llo[0][4]);
                }
                else "".Substring(2);
                return s;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.GetStaffByUserName", e.ToString());
                return null;
            }
        }

        public Staff GetStaffByID(int id)
        {
            try
            {
                DB.SetQuery("SELECT StaffID, StaffName, StaffUserName, Password, Position FROM Staff WHERE StaffID = @id");
                DB.AddParameters("id", id);
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                Staff s = new Staff();
                if (llo.Count > 0)
                {
                    s.StaffID = Convert.ToInt16(llo[0][0]);
                    s.StaffName = Convert.ToString(llo[0][1]);
                    s.StaffUserName = Convert.ToString(llo[0][2]);
                    s.Password = Convert.ToString(llo[0][3]);
                    s.Position = Convert.ToString(llo[0][4]);
                }
                else "".Substring(2);
                return s;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.GetStaffByID", e.ToString());
                return null;
            }
        }

        public List<Staff> GetAllStaff()
        {
            try
            {
                DB.SetQuery("SELECT StaffID, StaffName, StaffUserName, Password, Position FROM Staff");
                List<List<object>> llo = new List<List<object>>();
                llo = DB.ExeQuery();
                List<Staff> Staff = new List<Staff>();
                Staff s;
                if (llo.Count > 0)
                {
                    foreach (List<object> lo in llo)
                    {
                        s = new Staff();
                        s.StaffID = Convert.ToInt16(lo[0]);
                        s.StaffName = Convert.ToString(lo[1]);
                        s.StaffUserName = Convert.ToString(lo[2]);
                        s.Password = Convert.ToString(lo[3]);
                        s.Position = Convert.ToString(lo[4]);
                        Staff.Add(s);
                    }
                }
                else "".Substring(2);
                return Staff;
            }
            catch (Exception e)
            {
                Logger.Instance.Log("Cocbook_DAL.Staff.GetAllStaff", e.ToString());
                return null;
            }
        }
    }
}
